VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
END
Attribute VB_Name = "AWDataUtil"
Attribute VB_Creatable = True
Attribute VB_Exposed = True
Private adoConn As ADODB.Connection
Private Const varRows As Variant = 1
Private strSql As String                 ' SQL Command
    
Public Function AWCreate_Products()

    On Error Resume Next
    strSql = "DROP TABLE Products"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE Products (ProductCode varchar(10) NOT NULL PRIMARY KEY NONCLUSTERED, ProductType varchar(20) NOT NULL, ProductIntroductionDate datetime, ProductName varchar(50) NOT NULL, ProductDescription varchar(255) NOT NULL, ProductSize varchar(5), ProductImageURL varchar(255), UnitPrice float, OnSale bit, Discount int, ProductImage varchar(255))"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE  INDEX Products_byProductType ON Products(ProductType)"
    adoConn.Execute strSql, varRows, -1
    
    'Populate Products Table
    Open "Products.txt" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
        Input #1, ProductID, ProductCode, ProductType, ProductIntroductionDate, ProductName, ProductDescription, ProductSize, ProductImageURL, UnitPrice, OnSale, Discount, ProductImage
        strSql = "INSERT INTO Products " & _
                    "VALUES ('" & ProductCode & "', '" & ProductType & "', '" & _
                    ProductIntroductionDate & "', '" & ProductName & "', '" & _
                    ProductDescription & "', '" & ProductSize & "', '" & _
                    ProductImageURL & "', " & UnitPrice & ", " & _
                    OnSale & ", " & Discount & ", '" & ProductImage & "')"
        adoConn.Execute strSql, varRows, -1
    Loop
    Close #1    ' Close file.
    
    On Error Resume Next
    strSql = "DROP TABLE Inventory"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
        
    strSql = "CREATE TABLE Inventory (ProductCode char (10) NOT NULL PRIMARY KEY NONCLUSTERED, " & _
                "QuantityOnHand int NULL , ReorderQuantity int NULL)"
    adoConn.Execute strSql, varRows, -1
        
    'Populate Inventory Table
    Open "Inventory.txt" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
        Input #1, InventoryID, ProductCode, QuantityOnHand, ReorderQuantity
        strSql = "INSERT INTO Inventory " & _
                    "VALUES ('" & ProductCode & "', " & QuantityOnHand & ", " & ReorderQuantity & ")"
        adoConn.Execute strSql, varRows, -1
    Loop
    Close #1    ' Close file.

    On Error Resume Next
    strSql = "DROP TABLE InventoryManager"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE InventoryManager (Name varchar(64) NOT NULL, EMailAddress varchar(128) NULL)"
    adoConn.Execute strSql, varRows, -1
    
    On Error Resume Next
    strSql = "DROP TABLE ProductGroup"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE ProductGroup (ProductGroupCode varchar(20) NOT NULL PRIMARY KEY NONCLUSTERED, ProductGroupDesc varchar(50))"
    adoConn.Execute strSql, varRows, -1

    On Error Resume Next
    strSql = "DROP TABLE ProductType"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler

    strSql = "CREATE TABLE ProductType (ProductTypeCode varchar(20) NOT NULL PRIMARY KEY NONCLUSTERED, ProductGroupCode varchar(20) NOT NULL, ProductTypeDesc varchar(50))"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE INDEX ProductType_byProductGroup ON ProductType(ProductGroupCode)"
    adoConn.Execute strSql, varRows, -1
    
        'Populate the ProductGroup Table
    strSql = "INSERT INTO ProductGroup VALUES ('Camping', 'Camping Equipment')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductGroup VALUES ('Climbing', 'Climbing Equipment')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductGroup VALUES ('Clothing', 'Clothing')"
    adoConn.Execute strSql, varRows, -1
    
    'Populate the ProductType Table
    strSql = "INSERT INTO ProductType VALUES ('Backpack' , 'Camping', 'Backpack')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Boot' , 'Clothing', 'Boot')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Carabiner' , 'Climbing', 'Carabiner')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Crampon' , 'Climbing', 'Crampon')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Harness' , 'Climbing', 'Harness')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Pants' , 'Clothing', 'Pants')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Parka' , 'Clothing', 'Parka')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('RockShoes' ,'Climbing','Rock Shoes')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Shirt' , 'Clothing', 'Shirt')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('SleepingBag','Camping','Sleeping Bag')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Supplies' , 'Camping', 'Supplies')"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO ProductType VALUES ('Tent' , 'Camping', 'Tent')"
    adoConn.Execute strSql, varRows, -1

    Exit Function

ErrorHandler:

    Err.Raise Number:=Err.Number, Source:="AWCreate_Products", Description:=Err.Description
    Exit Function

End Function

    
Public Function AWCreate_Customers()

    On Error Resume Next
    strSql = "DROP TABLE Customers"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE Customers (CustomerID int IDENTITY (1, 1) NOT NULL PRIMARY KEY NONCLUSTERED, CustomerFirstName varchar (30) NULL , " & _
                "CustomerLastName varchar (30) NULL , BillingAddress varchar (255) NULL , City varchar (50) NULL , " & _
                "StateOrProvince varchar (20) NULL , PostalCode varchar (20) NULL , Country varchar (50) NULL , " & _
                "PhoneNumber varchar (30) NULL , EmailAddress varchar (50) NULL)"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE INDEX CustomerFullName ON Customers(CustomerLastName, CustomerFirstName)"
    adoConn.Execute strSql, varRows, -1
    
    'Populate Customers Table
    Open "Customers.txt" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
        Input #1, CustomerID, CustomerFirstName, CustomerLastName, BillingAddress, City, StateOrProvince, PostalCode, Country, PhoneNumber, EmailAddress
        strSql = "INSERT INTO Customers (CustomerFirstName, CustomerLastName, BillingAddress, City, StateOrProvince, PostalCode, Country, PhoneNumber, EmailAddress) " & _
                    "VALUES ('" & CustomerFirstName & "', '" & CustomerLastName & "', '" & _
                    BillingAddress & "', '" & City & "', '" & _
                    StateOrProvince & "', '" & PostalCode & "', '" & _
                    Country & "', '" & PhoneNumber & "', '" & EmailAddress & "')"
        adoConn.Execute strSql, varRows, -1
    Loop
    Close #1    ' Close file.
    
    Exit Function

ErrorHandler:

    Err.Raise Number:=Err.Number, Source:="AWCreate_Products", Description:=Err.Description
    Exit Function

    
End Function

Public Function AWCreate_Orders()

    On Error Resume Next
    strSql = "DROP TABLE Orders"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY NONCLUSTERED, CustomerID int NULL, OrderDate datetime NULL, " & _
                "ItemTotal float NULL, FreightCharge float NULL, SalesTax float NULL , " & _
                "OrderTotal float NULL)"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE INDEX Orders_byCustomer ON Orders(CustomerID)"
    adoConn.Execute strSql, varRows, -1
    
    On Error Resume Next
    strSql = "DROP TABLE OrderDetails"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE OrderDetails (OrderDetailID int NOT NULL, OrderID int NOT NULL, ProductCode varchar (10) NOT NULL, " & _
                "Quantity int NULL, Color varchar (30) NULL, Size varchar (30) NULL, DiscountedPrice float NULL, " & _
                "ExtPrice float NULL, " & _
                "CONSTRAINT OrderDetailPrimaryKey PRIMARY KEY CLUSTERED " & _
                "(OrderID, OrderDetailID))"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE INDEX OrderDetails_byProduct ON OrderDetails(ProductCode)"
    adoConn.Execute strSql, varRows, -1

    On Error Resume Next
    strSql = "DROP TABLE Payments"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE Payments (PaymentID int NOT NULL PRIMARY KEY CLUSTERED, OrderID int NOT NULL, PaymentAmount money NOT NULL, " & _
                "PaymentDate datetime NULL, CreditCardNumber varchar (30) NULL, CardholdersName varchar (50) NULL, " & _
                "CreditCardExpDate datetime NULL, CreditCardAuthorizationNumber varchar (30) NULL, PaymentMethodID int NOT NULL)"
    adoConn.Execute strSql, varRows, -1
    
    strSql = "CREATE  INDEX Payments_byPaymentMethod ON Payments(PaymentMethodID)"
    adoConn.Execute strSql, varRows, -1

    On Error Resume Next
    strSql = "DROP TABLE PaymentAuthorization"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler

    strSql = "CREATE TABLE PaymentAuthorization (PaymentType int NOT NULL PRIMARY KEY NONCLUSTERED, PaymentAuthorize bit NOT NULL)"
    adoConn.Execute strSql, varRows, -1
    
    On Error Resume Next
    strSql = "DROP TABLE SalesTaxTable"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler
    
    strSql = "CREATE TABLE SalesTaxTable (State char(2) NOT NULL PRIMARY KEY NONCLUSTERED, TaxRate float NOT NULL)"
    adoConn.Execute strSql, varRows, -1
    
    On Error Resume Next
    strSql = "DROP TABLE TakeANumber"
    adoConn.Execute strSql, varRows, -1
    On Error GoTo ErrorHandler

    
    strSql = "CREATE TABLE TakeANumber (PropertyGroupName varchar(255) NOT NULL PRIMARY KEY NONCLUSTERED, NextNumber int NOT NULL)"
    adoConn.Execute strSql, varRows, -1
    
    'Populate Sales Tax Table
    Open "SalesTax.txt" For Input As #1
    Do While Not EOF(1) ' Loop until end of file.
        Input #1, State, TaxRate
        strSql = "INSERT INTO SalesTaxTable " & _
                    "VALUES ('" & State & "', " & TaxRate & ")"
        adoConn.Execute strSql, varRows, -1
    Loop
    Close #1    ' Close file.
    
    strSql = "INSERT INTO TakeANumber VALUES ('OrderID' , 1000)"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO TakeANumber VALUES ('PaymentID' , 2000)"
    adoConn.Execute strSql, varRows, -1
    
    'Populate the Payment Authorization Table
    strSql = "INSERT INTO PaymentAuthorization VALUES (1, -1)"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO PaymentAuthorization VALUES (2, -1)"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO PaymentAuthorization VALUES (3, -1)"
    adoConn.Execute strSql, varRows, -1
    strSql = "INSERT INTO PaymentAuthorization VALUES (4, -0)"
    adoConn.Execute strSql, varRows, -1
   
    
    Exit Function

ErrorHandler:

    Err.Raise Number:=Err.Number, Source:="AWCreate_Products", Description:=Err.Description
    Exit Function

End Function

Private Sub Class_Initialize()
    
    Set adoConn = CreateObject("ADODB.Connection")
    adoConn.Open strConnect, "", ""

End Sub

